﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace IService.DataDLL
{
    /// <summary>
    /// 数据库底层的实现类
    /// </summary>
    public class OleSqlHelper : IDBHelper
    {
        #region 变量声明处
        public OleDbConnection Conn;
        public string connectionstring;
        public string connectionkey = "connectionString";
        #endregion


        #region 构造函数与连接关闭数据库
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="Dbpath">ACCESS数据库路径</param>
        public OleSqlHelper()
        {
            string ConnString = System.Configuration.ConfigurationManager.AppSettings[ConnectionKey];
            ConnectionString = ConnString;
            Conn = new OleDbConnection(ConnectionString);
            Conn.Open();
        }

        public OleSqlHelper(string connectionKey)
        {
            ConnectionKey = connectionKey;
            string ConnString = System.Configuration.ConfigurationManager.AppSettings[ConnectionKey];
            ConnectionString = ConnString;
            Conn = new OleDbConnection(ConnectionString);
            Conn.Open();
        }

        public string ConnectionKey
        {
            get { return this.connectionkey; }
            set { this.connectionkey = value; }
        }

        public string ConnectionString
        {
            get { return this.connectionstring; }
            set { this.connectionstring = value; }
        }

        /// <summary>
        /// 打开数据源链接
        /// </summary>
        /// <returns></returns>
        public OleDbConnection DbConn()
        {
            Conn.Open();
            return Conn;
        }

        /// <summary>
        /// 请在数据传递完毕后调用该函数，关闭数据链接。
        /// </summary>
        public void Close()
        {
            Conn.Close();
        }
        #endregion


        #region 数据库基本操作
        /// <summary>
        /// 根据SQL命令返回数据DataTable数据表,
        /// 可直接作为dataGridView的数据源
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public DataTable SelectToDataTable(string SQL)
        {
            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                OleDbCommand command = new OleDbCommand(SQL, Conn);
                adapter.SelectCommand = command;
                DataTable Dt = new DataTable();
                adapter.Fill(Dt);
                return Dt;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                this.Close();
            }
        }

        /// <summary>
        /// 根据SQL命令返回数据DataSet数据集，其中的表可直接作为dataGridView的数据源。
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="subtableName">在返回的数据集中所添加的表的名称</param>
        /// <returns></returns>
        public DataSet SelectToDataSet(string SQL)
        {
            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                OleDbCommand command = new OleDbCommand(SQL, Conn);
                adapter.SelectCommand = command;
                DataSet Ds = new DataSet();
                adapter.Fill(Ds);
                return Ds;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                this.Close();
            }
        }

        /// <summary>
        /// 在指定的数据集中添加带有指定名称的表，由于存在覆盖已有名称表的危险，返回操作之前的数据集。
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="subtableName">添加的表名</param>
        /// <param name="DataSetName">被添加的数据集名</param>
        /// <returns></returns>
        public DataSet SelectToDataSet(string SQL, string subtableName, DataSet DataSetName)
        {
            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                OleDbCommand command = new OleDbCommand(SQL, Conn);
                adapter.SelectCommand = command;
                DataTable Dt = new DataTable();
                DataSet Ds = new DataSet();
                Ds = DataSetName;
                adapter.Fill(DataSetName, subtableName);
                return Ds;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                this.Close();
            }
        }

        /// <summary>
        /// 根据SQL命令返回OleDbDataAdapter，
        /// 使用前请在主程序中添加命名空间System.Data.OleDb
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public OleDbDataAdapter SelectToOleDbDataAdapter(string SQL)
        {
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            try
            {
                OleDbCommand command = new OleDbCommand(SQL, Conn);
                adapter.SelectCommand = command;
                return adapter;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                this.Close();
            }
        }

        /// <summary>
        /// 执行SQL命令，不需要返回数据的修改，删除可以使用本函数
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public bool ExecuteSQLNonquery(string SQL)
        {
            OleDbCommand cmd = new OleDbCommand(SQL, Conn);
            try
            {
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
            finally
            {
                this.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句得到一个对象
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public object ExecuteScalar(string SQL)
        {
            OleDbCommand cmd = new OleDbCommand(SQL, Conn);
            try
            {
                object obj = cmd.ExecuteScalar();
                return obj;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                this.Close();
            }
        }
        /// <summary>
        /// 获取记录行数
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="WhereClause"></param>
        /// <returns></returns>
        public int GetRecordCount(string TableName, string WhereClause)
        {
            string SQL = "SELECT COUNT(*) FROM " + TableName;
            if (!string.IsNullOrEmpty(WhereClause))
            {
                SQL = SQL + " WHERE " + WhereClause;
            }
            object obj = ExecuteScalar(SQL);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }
        }
        #endregion

        #region Command方式数据访问
        /// <summary>
        /// 传入一个声明并赋值的Command对象，返回是否成功
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        public bool ExecuteSqlCommand(IDbCommand cmd)
        {
            try
            {
                cmd.Connection = Conn;
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
            finally
            {
                this.Close();
            }
        }

        //执行SQL通过Command，传递SqlParameter参数，返回DataSet类型结果
        public DataSet ExecuteDataSetWithCommand(string CommandText, params IDataParameter[] Parameters)
        {
            try
            {
                OleDbCommand command = new OleDbCommand();
                command.Connection = Conn;
                command.CommandText = CommandText;
                command.Parameters.Clear();
                if (Parameters != null)
                {
                    foreach (OleDbParameter parm in Parameters)
                    {
                        command.Parameters.Add(parm);
                    }
                }
                DataSet ds = null;
                OleDbDataAdapter da = new OleDbDataAdapter(command);
                ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            catch (Exception e)
            {
                return null;
            }
            finally
            {
                this.Close();
            }
        }
        /// <summary>
        /// 执行SQL通过Command，传递SqlParameter参数,返回DataTable类型结果
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTableWithCommand(string CommandText, params IDataParameter[] Parameters)
        {
            DataSet ds = ExecuteDataSetWithCommand(CommandText, Parameters);
            if (ds == null)
            {
                return null;
            }
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            return null;
        }
        /// <summary>
        /// 执行SQL通过Command，传递SqlParameter参数,返回object类型结果
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public object ExecuteScalarWithCommand(string CommandText, params IDataParameter[] Parameters)
        {
            DataSet ds = ExecuteDataSetWithCommand(CommandText, Parameters);
            if (ds == null)
            {
                return null;
            }
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0].Rows[0][0];
            }
            return null;
        }
        /// <summary>
        /// 执行SQL通过Command，传递SqlParameter参数,返回Int类型结果，影响行数
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public int ExecuteNonQueryWithCommand(string CommandText, params IDataParameter[] Parameters)
        {
            try
            {
                OleDbCommand command = new OleDbCommand();
                command.Connection = Conn;
                command.CommandText = CommandText;
                command.Parameters.Clear();
                if (Parameters != null)
                {
                    foreach (OleDbParameter parm in Parameters)
                    {
                        command.Parameters.Add(parm);
                    }
                }
                int result = command.ExecuteNonQuery();
                return result;
            }
            catch (Exception e)
            {
                return 0;
            }
            finally
            {
                this.Close();
            }
        }
        /// <summary>
        /// 执行SQL通过Command，传递SqlParameter参数，返回是否执行成功
        /// </summary>
        /// <param name="CommandText"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public bool ExecuteBoolWithCommand(string CommandText, params IDataParameter[] Parameters)
        {
            return ExecuteNonQueryWithCommand(CommandText, Parameters) > 0;
        }
        #endregion
    }
}
